import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class Game extends HttpServlet
{

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String dbUser = "jh3240"; // enter your username here
        String dbPassword = "kuzifQbw"; // enter your password here

        try
        {
            OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
            ods.setURL("jdbc:oracle:thin:@//w4111g.cs.columbia.edu:1521/ADB");
            ods.setUser(dbUser);
            ods.setPassword(dbPassword);

            Connection conn = ods.getConnection();

            String query = new String();
            Statement s = conn.createStatement();

            String gid = request.getParameter("gid");

            query = "SELECT ht.tid as htid, at.tid as atid, ht.name as hname, at.name as aname, g.homescore, g.awayscore, s.name as sname, s.country, s.state, s.city, g.played_on "
                    + "FROM games g, teams ht, teams at, owned_by o, stadiums s "
                    + "WHERE o.tid = ht.tid and o.sid = s.sid and ht.tid = g.hometeam and at.tid = g.awayteam and g.gid = '"
                    + gid + "'";

            ResultSet r = s.executeQuery(query);
            r.next();

            String sname = r.getString("sname");
            String hteam = r.getString("hname");
            String ateam = r.getString("aname");
            String country = r.getString("country");
            String state = r.getString("state");
            String city = r.getString("city");
            String htid = r.getString("htid");
            String atid = r.getString("atid");
            int hscore = r.getInt("homescore");
            int ascore = r.getInt("awayscore");
            String played_on = r.getDate("played_on").toString();

            out.println("<h1>" + ateam + " " + ascore + ":" + hscore + " " + hteam + "</h1>");
            out.println("<h2>" + sname + ", " + city + ", " + state + ", " + country + "</h2>");
            out.println("<h2>" + played_on + "</h2>");

            r.close();
            
            query = "SELECT p.name, p.playerid FROM plays_for pl, players p WHERE p.playerid = pl.playerid AND pl.tid = '" 
                    + atid + "' AND pl.since < TO_DATE('" + played_on 
                    + "', 'YYYY-MM-DD') AND pl.until > TO_DATE('" + played_on + "', 'YYYY-MM-DD')";
            
            r = s.executeQuery(query);
            
            out.println("<h2>Players of " + ateam + "</h2>");
            while (r.next())
            {
                out.println("<a href=\"/player?pid=" + r.getString("playerid") + "\">" + r.getString("name") + "</a><br />");
            }
            r.close();
            
            query = "SELECT p.name, p.playerid FROM plays_for pl, players p WHERE p.playerid = pl.playerid AND pl.tid = '" 
                    + htid + "' AND pl.since < TO_DATE('" + played_on 
                    + "', 'YYYY-MM-DD') AND pl.until > TO_DATE('" + played_on + "', 'YYYY-MM-DD')";
            
            r = s.executeQuery(query);
            
            out.println("<h2>Players of " + hteam + "</h2>");
            while (r.next())
            {
                out.println("<a href=\"/player?pid=" + r.getString("playerid") + "\">" + r.getString("name") + "</a><br />");
            }
            r.close();

            query = "SELECT pi.name AS pitcher, pi.playerid AS pitcherid, b.name AS batter, b.playerid AS batterid, p.result " + "FROM Plays p, Players pi, Players b "
                    + "WHERE p.pitcher = pi.playerid AND p.batter = b.playerid AND p.gid = '" + gid + "' "
                    + "ORDER BY p.playid";

            r = s.executeQuery(query);

            out.println("<h2>List of All Plays:</h2>");
            while (r.next())
            {
                String pitcherStr = "<a href=\"/player?pid=" + r.getString("pitcherid") + "\">" + r.getString("pitcher") + "</a>";
                String batterStr = "<a href=\"/player?pid=" + r.getString("batterid") + "\">" + r.getString("batter") + "</a>";
                out.println(pitcherStr + " pitches to " + batterStr + ". Result: "
                        + getResultStr(r.getInt("result")) + "<br />");
            }
            r.close();
            s.close();

            conn.close();

        }
        catch (Exception e)
        {
            out.println("The database could not be accessed.<br>");
            out.println("More information is available as follows:<br>");
            e.printStackTrace(out);
        }

    } // end doGet method

    private static boolean isDateValid(int year, int month, int day)
    {
        if (year > 2011 || year < 2005)
            return false;

        if (month > 12 || month < 1)
            return false;

        if (day < 1)
            return false;

        if ((month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12))
        {
            if (day > 31)
                return false;
        }
        else if (month == 2)
        {
            if (year % 4 == 0)
            {
                if (day > 29)
                    return false;
            }
            else
            {
                if (day > 28)
                    return false;
            }
        }
        else if (day > 30)
            return false;

        return true;
    }

    private static String getResultStr(int result)
    {
        switch (result)
        {
        case 1:
            return "base hit";
        case 2:
            return "out";
        case 3:
            return "other";
        default:
            return "unrecognized";
        }
    }

} // end DatabaseServlet class

